﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data.OleDb;
using System.Data;

namespace FOBTV.Web.DBUtility
{
	/// <summary>
	/// 数据库底层的实现类
	/// </summary>
	public class OleSqlHelper : IDBHelper
	{
		#region 变量声明处
		public OleDbConnection Conn;
		public string connectionstring;
		public string connectionkey = "connectionString";
		#endregion


		#region 构造函数与连接关闭数据库
		/// <summary>
		/// 构造函数
		/// </summary>
		/// <param name="Dbpath">ACCESS数据库路径</param>
		public OleSqlHelper()
		{
			string ConnString = System.Configuration.ConfigurationManager.AppSettings[ConnectionKey];
			ConnectionString = ConnString;
			Conn = new OleDbConnection(ConnectionString);
			Conn.Open();
		}

		public OleSqlHelper(string connectionKey)
		{
			ConnectionKey = connectionKey;
			string ConnString = System.Configuration.ConfigurationManager.AppSettings[ConnectionKey];
			ConnectionString = ConnString;
			Conn = new OleDbConnection(ConnectionString);
			Conn.Open();
		}

		public string ConnectionKey
		{
			get { return this.connectionkey; }
			set { this.connectionkey = value; }
		}

		public string ConnectionString
		{
			get { return this.connectionstring; }
			set { this.connectionstring = value; }
		}

		/// <summary>
		/// 打开数据源链接
		/// </summary>
		/// <returns></returns>
		public OleDbConnection DbConn()
		{
			Conn.Open();
			return Conn;
		}

		/// <summary>
		/// 请在数据传递完毕后调用该函数，关闭数据链接。
		/// </summary>
		public void Close()
		{
			Conn.Close();
		}
		#endregion


		#region 数据库基本操作
		/// <summary>
		/// 根据SQL命令返回数据DataTable数据表,
		/// 可直接作为dataGridView的数据源
		/// </summary>
		/// <param name="SQL"></param>
		/// <returns></returns>
		public DataTable SelectToDataTable(string SQL)
		{
			try
			{
				OleDbDataAdapter adapter = new OleDbDataAdapter();
				OleDbCommand command = new OleDbCommand(SQL, Conn);
				adapter.SelectCommand = command;
				DataTable Dt = new DataTable();
				adapter.Fill(Dt);
				return Dt;
			}
			catch (Exception e)
			{
				return null;
			}
			finally
			{
				this.Close();
			}
		}

		/// <summary>
		/// 根据SQL命令返回数据DataSet数据集，其中的表可直接作为dataGridView的数据源。
		/// </summary>
		/// <param name="SQL"></param>
		/// <param name="subtableName">在返回的数据集中所添加的表的名称</param>
		/// <returns></returns>
		public DataSet SelectToDataSet(string SQL)
		{
			try
			{
				OleDbDataAdapter adapter = new OleDbDataAdapter();
				OleDbCommand command = new OleDbCommand(SQL, Conn);
				adapter.SelectCommand = command;
				DataSet Ds = new DataSet();
				adapter.Fill(Ds);
				return Ds;
			}
			catch (Exception e)
			{
				return null;
			}
			finally
			{
				this.Close();
			}
		}

		/// <summary>
		/// 在指定的数据集中添加带有指定名称的表，由于存在覆盖已有名称表的危险，返回操作之前的数据集。
		/// </summary>
		/// <param name="SQL"></param>
		/// <param name="subtableName">添加的表名</param>
		/// <param name="DataSetName">被添加的数据集名</param>
		/// <returns></returns>
		public DataSet SelectToDataSet(string SQL, string subtableName, DataSet DataSetName)
		{
			try
			{
				OleDbDataAdapter adapter = new OleDbDataAdapter();
				OleDbCommand command = new OleDbCommand(SQL, Conn);
				adapter.SelectCommand = command;
				DataTable Dt = new DataTable();
				DataSet Ds = new DataSet();
				Ds = DataSetName;
				adapter.Fill(DataSetName, subtableName);
				return Ds;
			}
			catch (Exception e)
			{
				return null;
			}
			finally
			{
				this.Close();
			}
		}

		/// <summary>
		/// 根据SQL命令返回OleDbDataAdapter，
		/// 使用前请在主程序中添加命名空间System.Data.OleDb
		/// </summary>
		/// <param name="SQL"></param>
		/// <returns></returns>
		public OleDbDataAdapter SelectToOleDbDataAdapter(string SQL)
		{
			OleDbDataAdapter adapter = new OleDbDataAdapter();
			try
			{
				OleDbCommand command = new OleDbCommand(SQL, Conn);
				adapter.SelectCommand = command;
				return adapter;
			}
			catch (Exception e)
			{
				return null;
			}
			finally
			{
				this.Close();
			}
		}

		/// <summary>
		/// 执行SQL命令，不需要返回数据的修改，删除可以使用本函数
		/// </summary>
		/// <param name="SQL"></param>
		/// <returns></returns>
		public bool ExecuteSQLNonquery(string SQL)
		{
			OleDbCommand cmd = new OleDbCommand(SQL, Conn);
			try
			{
				cmd.ExecuteNonQuery();
				return true;
			}
			catch (Exception e)
			{
				return false;
			}
			finally
			{
				this.Close();
			}
		}

		/// <summary>
		/// 执行SQL语句得到一个对象
		/// </summary>
		/// <param name="SQL"></param>
		/// <returns></returns>
		public object ExecuteScalar(string SQL)
		{
			OleDbCommand cmd = new OleDbCommand(SQL, Conn);
			try
			{
				object obj = cmd.ExecuteScalar();
				return obj;
			}
			catch (Exception e)
			{
				return null;
			}
			finally
			{
				this.Close();
			}
		}
		/// <summary>
		/// 获取记录行数
		/// </summary>
		/// <param name="TableName"></param>
		/// <param name="WhereClause"></param>
		/// <returns></returns>
		public int GetRecordCount(string TableName, string WhereClause)
		{
			string SQL = "SELECT COUNT(*) FROM " + TableName;
			if (!string.IsNullOrEmpty(WhereClause))
			{
				SQL = SQL + " WHERE " + WhereClause;
			}
			object obj = ExecuteScalar(SQL);
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return int.Parse(obj.ToString());
			}
		}
		#endregion

		#region Command方式数据访问
		/// <summary>
		/// 传入一个声明并赋值的Command对象，返回是否成功
		/// </summary>
		/// <param name="cmd"></param>
		/// <returns></returns>
		public bool ExecuteSqlCommand(IDbCommand cmd)
		{
			try
			{
				cmd.Connection = Conn;
				cmd.ExecuteNonQuery();
				return true;
			}
			catch (Exception e)
			{
				return false;
			}
			finally
			{
				this.Close();
			}
		}

		//执行SQL通过Command，传递SqlParameter参数，返回DataSet类型结果
		public DataSet ExecuteDataSetWithCommand(string CommandText, params IDataParameter[] Parameters)
		{
			try
			{
				OleDbCommand command = new OleDbCommand();
				command.Connection = Conn;
				command.CommandText = CommandText;
				command.Parameters.Clear();
				if (Parameters != null)
				{
					foreach (OleDbParameter parm in Parameters)
					{
						command.Parameters.Add(parm);
					}
				}
				DataSet ds = null;
				OleDbDataAdapter da = new OleDbDataAdapter(command);
				ds = new DataSet();
				da.Fill(ds);
				return ds;
			}
			catch (Exception e)
			{
				return null;
			}
			finally
			{
				this.Close();
			}
		}
		/// <summary>
		/// 执行SQL通过Command，传递SqlParameter参数,返回DataTable类型结果
		/// </summary>
		/// <param name="CommandText"></param>
		/// <param name="Parameters"></param>
		/// <returns></returns>
		public DataTable ExecuteDataTableWithCommand(string CommandText, params IDataParameter[] Parameters)
		{
			DataSet ds = ExecuteDataSetWithCommand(CommandText, Parameters);
			if (ds == null)
			{
				return null;
			}
			if (ds.Tables.Count > 0)
			{
				return ds.Tables[0];
			}
			return null;
		}
		/// <summary>
		/// 执行SQL通过Command，传递SqlParameter参数,返回object类型结果
		/// </summary>
		/// <param name="CommandText"></param>
		/// <param name="Parameters"></param>
		/// <returns></returns>
		public object ExecuteScalarWithCommand(string CommandText, params IDataParameter[] Parameters)
		{
			DataSet ds = ExecuteDataSetWithCommand(CommandText, Parameters);
			if (ds == null)
			{
				return null;
			}
			if (ds.Tables.Count > 0)
			{
				return ds.Tables[0].Rows[0][0];
			}
			return null;
		}
		/// <summary>
		/// 执行SQL通过Command，传递SqlParameter参数,返回Int类型结果，影响行数
		/// </summary>
		/// <param name="CommandText"></param>
		/// <param name="Parameters"></param>
		/// <returns></returns>
		public int ExecuteNonQueryWithCommand(string CommandText, params IDataParameter[] Parameters)
		{
			try
			{
				OleDbCommand command = new OleDbCommand();
				command.Connection = Conn;
				command.CommandText = CommandText;
				command.Parameters.Clear();
				if (Parameters != null)
				{
					foreach (OleDbParameter parm in Parameters)
					{
						command.Parameters.Add(parm);
					}
				}
				int result = command.ExecuteNonQuery();
				return result;
			}
			catch (Exception e)
			{
				return 0;
			}
			finally
			{
				this.Close();
			}
		}
		/// <summary>
		/// 执行SQL通过Command，传递SqlParameter参数，返回是否执行成功
		/// </summary>
		/// <param name="CommandText"></param>
		/// <param name="Parameters"></param>
		/// <returns></returns>
		public bool ExecuteBoolWithCommand(string CommandText, params IDataParameter[] Parameters)
		{
			return ExecuteNonQueryWithCommand(CommandText, Parameters) > 0;
		}
		#endregion
	}
}